//-------------------------------------------------------------------
//文件名称：ERole.cs
//模块名称：ERole数据访问层
//功能说明：
//-----------------------------------------------------------------
//修改记录：
//修改人：Dawen
//修改时间：2019年8月9日
//-----------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ELearning.Models;
using ELearning.Models.ViewModel.Role;

namespace ELearning.DAL
{
    /// <summary>
    /// ERole数据访问层
    /// </summary>
	public partial class ERoleRepository : BaseRepository<ERole>
    {
        /// <summary>
        /// 获取角色下面的子节点
        /// </summary>
        /// <param name="roleId">角色id</param>
	    public List<ERole> GetRoleSubList(Guid roleId)
        {
            var sql = @";
    WITH    cte
        AS ( SELECT   *
                FROM     dbo.ERole
                WHERE    RoleID = @roleId
                UNION ALL
                SELECT   d.*
                FROM     cte c
                        INNER JOIN dbo.ERole d ON d.ParentId = c.RoleID AND d.IsDelete = 0
        )
    SELECT  *
    FROM    cte ";
            var args = new[] {new SqlParameter("@roleId",roleId)
            };
            return nContext.Database.SqlQuery<ERole>(sql, args).ToList();
        }

        /// <summary>
        /// 获取角色的父级节点
        /// </summary>
        /// <param name="roleId">角色id</param>
        public List<ERole> GetRoleParentList(Guid roleId)
        {
            var sql = @";
    WITH    cte
        AS ( SELECT   *
                FROM     dbo.ERole
                WHERE    RoleID = @roleId
                UNION ALL
                SELECT   d.*
                FROM     cte c
                        INNER JOIN dbo.ERole d ON d.RoleID = c.ParentId AND d.IsDelete = 0
        )
    SELECT  *
    FROM    cte ";
            var args = new[] {new SqlParameter("@roleId",roleId)
            };
            return nContext.Database.SqlQuery<ERole>(sql, args).ToList();
        }

        /// <summary>
        /// 获取角色不关联的用户列表
        /// </summary>
        public List<SysUserModel> GetRoleUserList(GetRoleUserRequest request)
        {
            var startIndex = (request.Page - 1) * request.PageSize + 1;
            var endIndex = request.PageSize * request.Page;

            var where = string.Empty;
            var whereDealer = string.Empty;

            if (!string.IsNullOrEmpty(request.UserType))
            {
                where = $"{where} AND u.UserType = '{request.UserType}'";
            }
            if (!string.IsNullOrEmpty(request.UserName))
            {
                where = $"{where} AND u.UserName LIKE N'%{request.UserName}%'";
            }
            if (!string.IsNullOrEmpty(request.LoginName))
            {
                where = $"{where} AND u.LoginName LIKE N'%{request.LoginName}%'";
            }
            if (!string.IsNullOrEmpty(request.Unumber))
            {
                where = $"{where} AND u.Unumber LIKE N'%{request.Unumber}%'";
            }

            if (request.AreaCode != 0)
            {
                whereDealer = $" AND d.AreaCode = {request.AreaCode}";
            }
            if (request.ProvinceCode != 0)
            {
                whereDealer = $"{whereDealer} AND d.ProvinceCode = {request.ProvinceCode}";
            }
            if (request.CityCode != 0)
            {
                whereDealer = $"{whereDealer} AND d.CityCode = {request.CityCode}";
            }

            //角色关联用户
            var sql = $@"
            SELECT
	            *
            FROM
	            (
		           SELECT
						ROW_NUMBER() OVER(ORDER BY b.CreateTime DESC) rn,
						b.*,						
						COUNT(1) OVER() Total,
						d.AreaCode,
						d.ProvinceCode,
						d.CityCode
				   FROM
					(
					 SELECT						
						u.*,r.RoleID
					FROM
						 dbo.RRoleUser r
						LEFT JOIN dbo.ESysUser u ON r.UserID = u.UserID
					WHERE
						u.IsDelete = 0 	AND r.RoleID = '{request.RoleId}'
						{where}
					)b
                    LEFT JOIN dbo.EDealerInfo d ON b.UserName = d.DealerCode
                    WHERE 
						d.IsDelete = 0 {whereDealer}
	            )a
            WHERE
	            a.rn BETWEEN {startIndex} AND {endIndex}";
            if (request.ReqestType == 1)
            {
                sql = $@"
                    SELECT
	                                *
                                FROM
	                                (
		                               SELECT
						                    ROW_NUMBER() OVER(ORDER BY b.CreateTime DESC) rn,
						                    b.*,						
						                    COUNT(1) OVER() Total,
						                    d.AreaCode,
						                    d.ProvinceCode,
						                    d.CityCode
				                       FROM
					                    (
					                     SELECT						
						                    u.*,r.RoleID
					                    FROM
						                    dbo.ESysUser u
						                    LEFT JOIN dbo.RRoleUser r ON r.UserID = u.UserID	AND r.RoleID = '{request.RoleId}'
					                    WHERE
						                    u.IsDelete = 0 AND r.RoleID IS NULL
						                    {where}
					                    )b
                                        LEFT JOIN dbo.EDealerInfo d ON b.UserID = d.DealerCode
                                        WHERE 
						                    d.IsDelete = 0 {whereDealer}
	                                )a
                                WHERE
	                                a.rn BETWEEN {startIndex} AND {endIndex}";
            }

            return nContext.Database.SqlQuery<SysUserModel>(sql).ToList();
        }

        /// <summary>
        /// 获取角色关联或者不关联的用户列表
        /// </summary>
        public List<SysUserModel> GetBatchRoleUserList(GetRoleUserRequest request)
        {
            var where = "AND r.RoleID IS NULL";
            var whereDealer = string.Empty;
            if (request.ReqestType == 0)
            {
                where = $"AND r.RoleID IS NOT NULL";
            }
            if (!string.IsNullOrEmpty(request.UserType))
            {
                where = $"{where} AND u.UserType = '{request.UserType}'";
            }
            if (!string.IsNullOrEmpty(request.UserName))
            {
                where = $"{where} AND u.UserName LIKE N'%{request.UserName}%'";
            }
            if (!string.IsNullOrEmpty(request.Unumber))
            {
                where = $"{where} AND u.Unumber LIKE N'%{request.Unumber}%'";
            }

            if (request.AreaCode != 0)
            {
                whereDealer = $" AND d.AreaCode = {request.AreaCode}";
            }
            if (request.ProvinceCode != 0)
            {
                whereDealer = $"{whereDealer} AND d.ProvinceCode = {request.ProvinceCode}";
            }
            if (request.CityCode != 0)
            {
                whereDealer = $"{whereDealer} AND d.CityCode = {request.CityCode}";
            }


            var sql = $@"
                  SELECT
						b.*,						
						COUNT(1) OVER() Total,
						d.AreaCode,
						d.ProvinceCode,
						d.CityCode
				   FROM
					(
					 SELECT						
						DISTINCT u.UserID UserID,
                        u.LoginName,
                        u.UserName,
                        u.UserType,
                        u.OrgCode,
                        u.Unumber,
                        u.Password,
                        u.Sex,
                        u.Birthday,
                        u.HeadPhoto,
                        u.Company,
                        u.Department,
                        u.Position,
                        u.OfficeTel,
                        u.MobilePhone,
                        u.Email,
                        u.IsDelete,
                        u.CardNo,r.RoleID
					FROM
						dbo.ESysUser u
						LEFT JOIN dbo.RRoleUser r ON r.UserID = u.UserID  AND r.RoleID = '{request.RoleId}'
					WHERE
						u.IsDelete = 0 		
						{where}
					)b
                    LEFT JOIN dbo.EDealerInfo d ON b.UserID = d.DealerCode
                    WHERE 
						d.IsDelete = 0 {whereDealer}";

            return nContext.Database.SqlQuery<SysUserModel>(sql).ToList();
        }

    }
}


